package vip.xiaomoli.courier.dao;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import vip.xiaomoli.courier.dao.sql.Sql;
import vip.xiaomoli.courier.dao.sql.Sql_MySql;
import vip.xiaomoli.courier.model.TaskInfo;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CourierDao {

    private DataSource dataSource;

    private Sql sql;

    public CourierDao(DataSource dataSource) {
        this.dataSource = dataSource;
        Connection conn = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            String driverName = conn.getMetaData().getDriverName().toUpperCase();
            if (driverName.contains(Sql.TYPE_MYSQL)) {
                sql = new Sql_MySql();
            } else if (driverName.contains(Sql.TYPE_ORACLE)) {
                // TODO sql = new Sql_Oracle();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public long insertTaskInfo(TaskInfo ti) {
        long id = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.INSERT_TASK_INFO(), Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, ti.getCourierName());
            pstmt.setString(2, ti.getParam());
            pstmt.setTimestamp(3, new Timestamp(ti.getExecTime().getTime()));
            pstmt.setInt(4, ti.getLoadFlag());
            pstmt.setTimestamp(5, new Timestamp(ti.getLoadTime().getTime()));
            pstmt.setString(6, ti.getExecServerIP());
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();
            if (rs.next()) id = rs.getLong(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return id;
    }

    public void recordExecFail(TaskInfo ti) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.RECORD_EXEC_FAIL());
            String failMessage = ti.getFailMessage();
            pstmt.setString(1, failMessage.length()>2000 ? failMessage.substring(0, 2000) : failMessage);
            pstmt.setInt(2, ti.getFailTimes());
            pstmt.setTimestamp(3, new Timestamp(ti.getExecBgnTime().getTime()));
            pstmt.setTimestamp(4, new Timestamp(ti.getExecEndTime().getTime()));
            pstmt.setLong(5, ti.getExecMillis());
            pstmt.setLong(6, ti.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public void recordExecSuccess(TaskInfo ti) {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            conn.setAutoCommit(false);
            pstmt1 = conn.prepareStatement(sql.RECORD_EXEC_SUCCESS());
            pstmt1.setTimestamp(1, new Timestamp(ti.getExecBgnTime().getTime()));
            pstmt1.setTimestamp(2, new Timestamp(ti.getExecEndTime().getTime()));
            pstmt1.setLong(3, ti.getExecMillis());
            pstmt1.setLong(4, ti.getId());
            pstmt2 = conn.prepareStatement(sql.DELETE_TASK_INFO());
            pstmt2.setLong(1, ti.getId());
            pstmt1.executeUpdate();
            pstmt2.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            JdbcUtils.closeStatement(pstmt2);
            JdbcUtils.closeStatement(pstmt1);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public void unloadTasks(String ip) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.UNLOAD_TASKS());
            pstmt.setString(1, ip);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public void recordTaskLoad(TaskInfo ti) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.RECORD_TASK_LOAD());
            pstmt.setString(1, ti.getExecServerIP());
            pstmt.setLong(2, ti.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public List<TaskInfo> listTaskInfo4Exec(Date execTimeUB, Date execEndTimeUB, Date loadTimeUB, int clusterNum, int pos) {
        List<TaskInfo> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.LIST_TASK_INFO_4EXEC());
            pstmt.setTimestamp(1, new Timestamp(execTimeUB.getTime()));
            pstmt.setTimestamp(2, new Timestamp(execEndTimeUB.getTime()));
            pstmt.setInt(3, clusterNum);
            pstmt.setInt(4, pos);
            pstmt.setTimestamp(5, new Timestamp(loadTimeUB.getTime()));
            pstmt.setInt(6, clusterNum);
            pstmt.setInt(7, pos);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                TaskInfo ti = getTaskInfo4Exec(rs);
                list.add(ti);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return list;
    }

    private TaskInfo getTaskInfo4Exec(ResultSet rs) throws SQLException {
        TaskInfo ti = new TaskInfo();
        ti.setId(rs.getLong("id"));
        ti.setCourierName(rs.getString("courierName"));
        ti.setParam(rs.getString("param"));
        ti.setExecTime(rs.getTimestamp("execTime"));
        ti.setFailTimes(rs.getInt("failTimes"));
        return ti;
    }

    public int countTaskInfo(int succFlag) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            if (0 == succFlag) {
                pstmt = conn.prepareStatement(sql.COUNT_TASK_INFO());
            } else {
                pstmt = conn.prepareStatement(sql.COUNT_TASK_INFO_SUCC());
            }
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return 0;
    }

    public List<TaskInfo> pageTaskInfo(int succFlag, int offset, int limit) {
        List<TaskInfo> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            if (0 == succFlag) {
                pstmt = conn.prepareStatement(sql.PAGE_TASK_INFO());
            } else {
                pstmt = conn.prepareStatement(sql.PAGE_TASK_INFO_SUCC());
            }
            pstmt.setInt(1, offset);
            pstmt.setInt(2, limit);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                TaskInfo ti = new TaskInfo();
                ti.setId(rs.getLong("id"));
                ti.setCourierName(rs.getString("courierName"));
                ti.setParam(rs.getString("param"));
                ti.setExecTime(rs.getTimestamp("execTime"));
                ti.setExecServerIP(rs.getString("execServerIP"));
                ti.setExecEndTime(rs.getTimestamp("execEndTime"));
                ti.setExecMillis(rs.getLong("execMillis"));
                ti.setFailTimes(rs.getInt("failTimes"));
                ti.setFailMessage(rs.getString("failMessage"));
                ti.setDelFlag(rs.getInt("delFlag"));
                list.add(ti);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return list;
    }

    public String getFailMessage(int succFlag, long id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            if (0 == succFlag) {
                pstmt = conn.prepareStatement(sql.GET_FAIL_MESSAGE());
            } else {
                pstmt = conn.prepareStatement(sql.GET_FAIL_MESSAGE_SUCC());
            }
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return null;
    }

    public List<TaskInfo> getTaskInfos4Exec(List<Long> ids) {
        List<TaskInfo> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql.GET_TASK_INFOS_4EXEC(ids));
            while (rs.next()) {
                TaskInfo ti = getTaskInfo4Exec(rs);
                list.add(ti);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(stmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return list;
    }

    public String getParam(int succFlag, long id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            if (0 == succFlag) {
                pstmt = conn.prepareStatement(sql.GET_PARAM());
            } else {
                pstmt = conn.prepareStatement(sql.GET_PARAM_SUCC());
            }
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return null;
    }

    public void updateParam(String newData, long id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.UPDATE_PARAM());
            pstmt.setString(1, newData);
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public int deleteSucc(Date execEndTimeUB) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql.DELETE_SUCC());
            pstmt.setTimestamp(1, new Timestamp(execEndTimeUB.getTime()));
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
        return 0;
    }

    // TODO cluster测试
    // TODO 告警设计
    // TODO 分module

}
